package edu.tms.dao;


import java.util.List;
import java.util.Map;

import edu.tms.bean.Homework;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 作业信息表dao
 * @author Administrator
 *
 */
public class HomeworkDaoImp implements CommnonDao<Homework>{
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(Homework value) throws Exception {
		String sql="insert into t_homework values(?,?,?,?,?,?,?,?,?,?)";
		return JdbcUtil.insertAuto(sql,null,value.getTchId(),
				value.getMajorName(),
				value.getClassName(),
				value.getCourseName(),
				value.getCreateTime(),
				value.getHomeworkContent(),
				value.getHomeworkFile(),
				value.getHomeworkDeadline(),
				value.getSubmitState());
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Homework value) throws Exception {
		String sql="update t_homework set major_name=?,class_name=?,course_name=?,create_time=?,homework_content=?,"
				+ "homework_file=?,homework_deadline=?,submit_state=? where homework_id=?";
		return JdbcUtil.update(sql,
				value.getMajorName(),
				value.getClassName(),
				value.getCourseName(),
				value.getCreateTime(),
				value.getHomeworkContent(),
				value.getHomeworkFile(),
				value.getHomeworkDeadline(),
				value.getSubmitState(),
				value.getHomeworkId());			
	}
	
	/**
	 * 连表删除
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Homework value) throws Exception {
		return 0;
	}
	
	/**
	 * 删除内容
	 * @param value
	 * @throws Exception
	 */
	public int delete(Integer value) throws Exception {
			String sql="DELETE h, ws,wq FROM t_homework AS h"
					+ " LEFT JOIN t_work_submit AS ws ON ws.homeword_id=h.homework_id "
					+ " LEFT JOIN t_work_quality AS wq ON wq.work_submit_id=ws.work_submit_id "
					+ " WHERE h.homework_id=?;";
		return JdbcUtil.update(sql,value);
	}
	
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Homework> findAll(int pageNo, int pageSize) throws Exception {
		String sql = "SELECT st.stu_name,ho.homework_content,ho.create_time,wq.work_level "
			+"FROM t_student AS st ,t_homework AS ho ,t_work_submit AS ws ,t_work_quality AS wq "
			+"WHERE st.stu_id = ws.stu_id AND ho.homework_id = ws.work_submit_id "
			+"AND ws.homeword_id = wq.work_submit_id limit ?,?";
		List<Homework> list=JdbcUtil.query(Homework.class, sql,(pageNo-1)*pageSize,pageNo*pageSize);
		return list;
	}
	
	
	/**
	 * 根据小组查询成员作业
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> findBySquad(int squadId, int pageNo, int pageSize) throws Exception {
		String sql = "SELECT st.stu_name, hw.homework_content ,ws.create_time , wq.work_level "
				+"FROM t_work_submit AS ws , t_homework AS hw,t_work_quality AS wq ,t_student AS st "
				+"WHERE ws.homeword_id = hw.homework_id AND ws.homeword_id = wq.work_submit_id "
				+"AND st.stu_id = ws.stu_id AND ws.stu_id "
				+"IN(SELECT st.stu_id FROM t_student AS st WHERE st.stu_squad_id = ?) limit ?,? ";
		List<Homework> list=JdbcUtil.query(Homework.class, sql, squadId, (pageNo-1)*pageSize,pageSize);
		return list;
	}
	
	/**
	 * 查找单一实体
	 * @param Key
	 * @throws Exception
	 */
	@Override
	public Homework findByKey(Object Key) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		return null;
	}
	/**
	 * 根据学生班级查询
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Homework> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT * FROM t_homework AS h"
				+ " LEFT JOIN t_teacher AS t ON t.tch_id=h.tch_id"
				+ " WHERE h.class_name=?"
				+ " LIMIT ?,?;";
		List<Homework> list=JdbcUtil.query(Homework.class, sql,key,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	
	/**
	 * 根据老师姓名和学生班级查询
	 * @param stuClassName 学生班级
	 * @param content 老师姓名或科目名称
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> findAll(String stuClassName, String content, int pageNo, int pageSize) throws Exception {
		String sql="SELECT * FROM t_homework"
				+ " LEFT JOIN t_teacher ON t_teacher.tch_id=t_homework.tch_id"
				+ " WHERE t_homework.class_name=? AND t_teacher.tch_name=?"
				+ " or t_homework.course_name=?"
				+ " LIMIT ?,?;";
		List<Homework> list=JdbcUtil.query(Homework.class, sql,stuClassName,
				content,content,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	/**
	 * 根据学生班级查询数据总页数
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public long selectPage(int pageSize,Object stuClassName) throws Exception{
		String sql="SELECT count(*) FROM t_homework"
				+ " LEFT JOIN t_teacher ON t_teacher.tch_id=t_homework.tch_id"
				+ " WHERE t_homework.class_name=?";
		long page=(long) JdbcUtil.uniqueValue(sql,1,stuClassName);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	//根据老师姓名和学生班级查询数据总页数
	public long selectPage(int pageSize,String stuClassName,String content) throws Exception{
		String sql="SELECT count(*) FROM t_homework"
				+ " LEFT JOIN t_teacher ON t_teacher.tch_id=t_homework.tch_id"
				+ " WHERE t_homework.class_name=? AND t_teacher.tch_name=?"
				+ " or t_homework.course_name=?";
		long page=(long) JdbcUtil.uniqueValue(sql,1,stuClassName,content,content);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	/**
	 * 根据老师编号查询作业
	 * @param tchId 老师编号
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception 
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> findByTchId(Integer tchId, int pageNo, int pageSize) throws Exception {
		String sql="SELECT h.* FROM t_homework AS h"
				+ " LEFT JOIN t_teacher AS t ON t.tch_id=h.tch_id"
				+ " WHERE t.tch_id=?"
				+ " LIMIT ?,?;";
		List<Homework> hws=JdbcUtil.query(Homework.class, sql, tchId,(pageNo-1)*pageSize,pageSize);
		return hws;
	}
	
	//根据根据老师编号和班级查询作业
	@SuppressWarnings("unchecked")
	public List<Homework> findByTchId(Integer tchId, String content, int pageNo, int pageSize) throws Exception {
		String sql="SELECT t_homework.* FROM t_homework"
				+ " LEFT JOIN t_teacher ON t_teacher.tch_id=t_homework.tch_id"
				+ " WHERE t_homework.class_name=? AND t_teacher.tch_id=?"
				+ " LIMIT ?,?;";
		List<Homework> list=JdbcUtil.query(Homework.class, sql,content,
				tchId,(pageNo-1)*pageSize,pageSize);
		return list;
	}
	
	/**
	 * 根据作业编号查询该作业已提交作业人数
	 * @param homeworkId
	 * @return
	 * @throws Exception
	 */
	public double countWork(Integer homeworkId) throws Exception{
		String sql="SELECT COUNT(*) FROM t_work_submit WHERE homeword_id=?;";
		long count=(long) JdbcUtil.uniqueValue(sql, 1, homeworkId);
		double c=(double)count;
		return c;
	}

	/**
	 * 根据老师编号查询数据总页数
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public long selectTchIdPage(int pageSize,Integer tchId) throws Exception{
		String sql="SELECT count(*) FROM t_homework"
				+ " LEFT JOIN t_teacher ON t_teacher.tch_id=t_homework.tch_id"
				+ " WHERE t_teacher.tch_id=?";
		long page=(long) JdbcUtil.uniqueValue(sql,1,tchId);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	//根据根据老师编号和班级查询数据总页数
	public long selectTchIdPage(Integer tchId,String content,int pageSize) throws Exception{
		String sql="SELECT count(*) FROM t_homework  "
				+ " LEFT JOIN t_teacher ON t_teacher.tch_id=t_homework.tch_id"
				+ " WHERE t_teacher.tch_id=? and t_homework.class_name=?";
		long page=(long) JdbcUtil.uniqueValue(sql,1,tchId,content);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Homework> findByStuName(String stuName, int squadId, int pageNo, int pageSize) throws Exception {
		String sql = "SELECT st.stu_name, hw.homework_content ,ws.create_time , wq.work_level "
				+"FROM t_work_submit AS ws , t_homework AS hw,t_work_quality AS wq ,t_student AS st "
				+"WHERE ws.homeword_id = hw.homework_id AND ws.homeword_id = wq.work_submit_id "
				+"AND st.stu_id = ws.stu_id "
				+"AND st.stu_name = ? AND st.stu_squad_id = ? limit ?,?";
		List<Homework> Homework=JdbcUtil.query(Homework.class, sql, stuName, squadId, (pageNo-1)*pageSize,pageSize);
		return Homework;
	}
	
	public long getPages(int pageSize) throws Exception{
		String sql="select count(*) "
			+"from t_student AS st ,t_homework AS ho ,t_work_submit AS ws ,t_work_quality AS wq " 
			+"WHERE st.stu_id = ws.stu_id AND ho.homework_id = ws.work_submit_id " 
			+"AND ws.homeword_id = wq.work_submit_id";
		long counts = (long) JdbcUtil.uniqueValue(sql,1);
		return counts%pageSize==0?counts/pageSize:counts/pageSize+1;
	}
	
	/**
	 * 查找班级作业数据
	 * 邱干 学习委员管理
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> findDatumByClass(String className, int pageNo, int pageSize) throws Exception {
	String sql="SELECT th.`homework_id` AS homeworkId,course_name AS courseName," 
		   +"(SELECT COUNT(ts.stu_id)FROM t_work_quality tq,t_work_submit tws,t_student ts WHERE "
		   +" tws.`work_submit_id`=tq.`work_submit_id` AND tws.`stu_id`=ts.`stu_id` AND tws.homeword_id=homeworkId) AS shouldsubcount, "
		   +"(SELECT COUNT(*) FROM t_work_quality tq,t_work_submit tws,t_student ts WHERE " 
		   +"tws.`work_submit_id`=tq.`work_submit_id` AND tws.`stu_id`=ts.`stu_id` AND tws.homeword_id=homeworkId)" 
		   +"AS workcount,(SELECT COUNT(*)FROM t_work_quality tq,t_work_submit ts WHERE" 
		   +" ts.`work_submit_id`=tq.`work_submit_id`AND (tq.`work_level`='A' OR tq.`work_level`='B' " 
		   +"OR tq.`work_level`='C' )AND ts.`homeword_id`=homeworkId) AS passcount,"
		   +"(SELECT COUNT(*)FROM t_work_quality tq,t_work_submit ts WHERE ts.`work_submit_id`=tq.`work_submit_id`"
		   +" AND (tq.`work_level`='D' OR tq.`work_level`='E')AND ts.`homeword_id`=homeworkId) AS notpasscount,"
		   +"(SELECT(CONCAT(TRUNCATE((SELECT((SELECT COUNT(*)FROM t_work_quality tq, t_work_submit ts WHERE "
		   +" ts.`work_submit_id`=tq.`work_submit_id` AND (tq.`work_level`='A' OR"
		   +" tq.`work_level`='B' OR tq.`work_level`='C') AND ts.`homeword_id`=homeworkId)/(SELECT COUNT(ts.stu_id)" 
		   +" FROM t_work_quality tq,t_work_submit tws,t_student ts WHERE tws.`work_submit_id`=tq.`work_submit_id` " 
		   +" AND tws.`stu_id`=ts.`stu_id` AND tws.homeword_id=homeworkId) )) * 100,2))"
	   	   +"))AS percentpass FROM t_homework th,t_work_submit tws,t_work_quality tq, " 
	   	   +"t_student ts WHERE th.`homework_id`=tws.`homeword_id` AND "
		   +"tws.`work_submit_id`=tq.`work_submit_id` AND tws.`stu_id`=ts.`stu_id` AND ts.stu_class_name =? " 
		   +"GROUP BY homeworkId limit ?,?";
		List<Homework> list=JdbcUtil.query(Homework.class, sql, className,(pageNo-1)*pageSize,pageNo*pageSize);
		return list;
	}
	
	/**
	 * 根据课程名或专业名查询作业数据
	 * 邱干 学习委员管理
	 * @param name
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> findDatumByName(String className,String name, int pageNo, int pageSize) throws Exception {
	String sql="SELECT th.`homework_id` AS homeworkId,course_name AS courseName," 
		   +"(SELECT COUNT(ts.stu_id)FROM t_work_quality tq,t_work_submit tws,t_student ts WHERE "
		   +" tws.`work_submit_id`=tq.`work_submit_id` AND tws.`stu_id`=ts.`stu_id` AND tws.homeword_id=homeworkId) AS shouldsubcount, "
		   +"(SELECT COUNT(*) FROM t_work_quality tq,t_work_submit tws,t_student ts WHERE " 
		   +"tws.`work_submit_id`=tq.`work_submit_id` AND tws.`stu_id`=ts.`stu_id` AND tws.homeword_id=homeworkId)" 
		   +"AS workcount,(SELECT COUNT(*)FROM t_work_quality tq,t_work_submit ts WHERE" 
		   +" ts.`work_submit_id`=tq.`work_submit_id`AND (tq.`work_level`='A' OR tq.`work_level`='B' " 
		   +"OR tq.`work_level`='C' )AND ts.`homeword_id`=homeworkId) AS passcount,"
		   +"(SELECT COUNT(*)FROM t_work_quality tq,t_work_submit ts WHERE ts.`work_submit_id`=tq.`work_submit_id`"
		   +" AND (tq.`work_level`='D' OR tq.`work_level`='E')AND ts.`homeword_id`=homeworkId) AS notpasscount,"
		   +"(SELECT(CONCAT(TRUNCATE((SELECT((SELECT COUNT(*)FROM t_work_quality tq, t_work_submit ts WHERE "
		   +" ts.`work_submit_id`=tq.`work_submit_id` AND (tq.`work_level`='A' OR"
		   +" tq.`work_level`='B' OR tq.`work_level`='C') AND ts.`homeword_id`=homeworkId)/(SELECT COUNT(ts.stu_id)" 
		   +" FROM t_work_quality tq,t_work_submit tws,t_student ts WHERE tws.`work_submit_id`=tq.`work_submit_id` " 
		   +" AND tws.`stu_id`=ts.`stu_id` AND tws.homeword_id=homeworkId) )) * 100,2))"
	   	   +"))AS percentpass FROM t_homework th,t_work_submit tws,t_work_quality tq, " 
	   	   +"t_student ts WHERE th.`homework_id`=tws.`homeword_id` AND "
		   +"tws.`work_submit_id`=tq.`work_submit_id` AND tws.`stu_id`=ts.`stu_id` AND ts.stu_class_name =? " 
		   +" AND (th.`course_name`=? OR th.`major_name`=?)GROUP BY homeworkId limit ?,? ";
		List<Homework> list=JdbcUtil.query(Homework.class, sql,className,name,name,(pageNo-1)*pageSize,pageNo*pageSize);
		return list;
	}
	
	/**
	 * 查询课程名 用于下拉框选项
	 * 邱干 学习委员管理
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> worknames(String className) throws Exception{
		String sql="SELECT DISTINCT major_name AS majorName,course_name AS courseName FROM " 
				+"t_homework th,t_student ts,t_work_submit tws WHERE"
				+" th.`homework_id`=tws.`homeword_id` AND tws.`stu_id`=ts.`stu_id` AND ts.`stu_class_name`=?";
		return  JdbcUtil.query(Homework.class, sql,className);
	}
	
	/**
	 * 根据作业编号查询该作业合格人数
	 * @param homeworkId
	 * @return
	 * @throws Exception
	 */
	public double countScore(Integer homeworkId)throws Exception{
		String sqlA="SELECT count(*) FROM t_work_submit"
				+ " LEFT JOIN t_work_quality ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " WHERE t_work_quality.work_level='A' and homeword_id=?;";
		long countA=(long) JdbcUtil.uniqueValue(sqlA, 1, homeworkId);
		
		String sqlB="SELECT count(*) FROM t_work_submit"
				+ " LEFT JOIN t_work_quality ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " WHERE t_work_quality.work_level='B' and homeword_id=?;";
		long countB=(long) JdbcUtil.uniqueValue(sqlB, 1, homeworkId);
		
		String sqlC="SELECT count(*) FROM t_work_submit"
				+ " LEFT JOIN t_work_quality ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " WHERE t_work_quality.work_level='C' and homeword_id=?;";
		long countC=(long) JdbcUtil.uniqueValue(sqlC, 1, homeworkId);
		
		String sqlD="SELECT count(*) FROM t_work_submit"
				+ " LEFT JOIN t_work_quality ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " WHERE t_work_quality.work_level='D' and homeword_id=?;";
		long countD=(long) JdbcUtil.uniqueValue(sqlD, 1, homeworkId);
		double a=(double)countA;
		double b=(double)countB;
		double c=(double)countC;
		double d=(double)countD;
		return a+b+c+d;
	}
	
	/**
	 * 查询作业不合格的人员
	 * @param homeworkId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> queryUnquali(Integer homeworkId,Integer pageNo,Integer pageSize) throws Exception{
		String sqla="SELECT h.class_name,h.homework_content,s.stu_name,q.work_level,w.work_state"
				+ " FROM t_work_quality AS q"
				+ " LEFT JOIN t_work_submit AS w ON w.work_submit_id=q.work_submit_id"
				+ " LEFT JOIN t_homework AS h ON h.homework_id=w.homeword_id"
				+ " LEFT JOIN t_student AS s ON s.stu_id=w.stu_id"
				+ " WHERE q.work_level='E' AND h.homework_id=?"
				+ " limit ?,?";
		List<Homework> hws=JdbcUtil.query(Homework.class, sqla, homeworkId,(pageNo-1)*pageSize,pageSize);
		return hws;
	}

	/**
	 * 统计作业不合格的人员个数
	 * @param homeworkId
	 * @return
	 * @throws Exception
	 */
	public long queryUnqualiPage(int pageSize,Integer homeworkId) throws Exception{
		String sqla="SELECT count(*)"
				+ " FROM t_work_quality AS q"
				+ " LEFT JOIN t_work_submit AS w ON w.work_submit_id=q.work_submit_id"
				+ " LEFT JOIN t_homework AS h ON h.homework_id=w.homeword_id"
				+ " LEFT JOIN t_student AS s ON s.stu_id=w.stu_id"
				+ " WHERE q.work_level='E' AND h.homework_id=?";
		long page=(long) JdbcUtil.uniqueValue(sqla,1,homeworkId);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	/**
	 * 统计作业未提交的人员个数
	 * @param homeworkId
	 * @return
	 * @throws Exception
	 */
	public Long queryUnsubmitPage(int pageSize, Integer homeworkId,String className) throws Exception {
		String sql="SELECT count(*)"
				+ " FROM t_student as s"
				+ " LEFT JOIN t_work_submit as ws ON s.stu_id = ws.stu_id "
				+ " LEFT JOIN t_work_quality as wq ON ws.work_submit_id = wq.work_submit_id"
				+ " LEFT JOIN t_homework as h ON ws.homeword_id = h.homework_id"
				+ " WHERE s.stu_id NOT IN("
				+ " SELECT stu_id FROM t_work_submit WHERE homework_id = ?) AND s.stu_class_name=?;";
		long page=(long) JdbcUtil.uniqueValue(sql,1,homeworkId,className);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	/**
	 * 统计作业未提交的人员
	 * @param homeworkId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Homework> queryUnsubmission(Integer homeworkId,String className,Integer pageNo,Integer pageSize) throws Exception {
		String sql="SELECT s.stu_class_name,h.homework_content, s.stu_name,wq.work_level,ws.work_state "
				+ " FROM t_student as s"
				+ " LEFT JOIN t_work_submit as ws ON s.stu_id = ws.stu_id "
				+ " LEFT JOIN t_work_quality as wq ON ws.work_submit_id = wq.work_submit_id"
				+ " LEFT JOIN t_homework as h ON ws.homeword_id = h.homework_id"
				+ " WHERE s.stu_id NOT IN(SELECT stu_id FROM t_work_submit WHERE homework_id=?)"
				+ " AND s.stu_class_name=? and h.homework_id=?"
				+ " limit ?,?;";
		List<Homework> hws=JdbcUtil.query(Homework.class, sql, homeworkId,className,homeworkId,(pageNo-1)*pageSize,pageSize);
		return hws;
	}

}
